package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Department;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class ReportAgePeriod extends SvrProcess {
	
	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private int HR_Department_ID = 0;
	/** */
	private int HR_Block_ID = 0;
	/** */
	private String path = "";
	/** */
	X_HR_Department department = null;
	/** */
	X_HR_Department block = null;
	/** */
	private List<Object> param = new ArrayList<Object>();

	@Override
	protected void prepare() {

		log.info(" Report Age period ");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
	
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo("HR_Block_ID") == 0 && para[i].getParameter() != null)
				HR_Block_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("HR_Department_ID") == 0 && para[i].getParameter() != null)
				HR_Department_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");
		
		department = new X_HR_Department(getCtx(), HR_Department_ID, null);
		block = new X_HR_Department(getCtx(), HR_Block_ID, null);

	}

	@Override
	protected String doIt() throws Exception {

		// Types of cells for
		Label label;
		Number number;
		Formula formula;
		// There are checks for a specified pattern
		String templateName = "SampleAGE";
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
		
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("Age period").append(fileExtension);
		
		// 
		File templateCopy = new File(fullPath.toString());
		File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		WritableSheet sheet1 = null;
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
			sheet1 = copy.getSheet(1);
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		    return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Standart style cell
	    WritableCellFormat borderStyle = new WritableCellFormat();
	    borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    borderStyle.setAlignment(Alignment.CENTRE);
	    borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    borderStyle.setWrap(true);
	    // Job style cell
	    WritableCellFormat jobStyle = new WritableCellFormat();
	    jobStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    jobStyle.setAlignment(Alignment.LEFT);
	    jobStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    jobStyle.setWrap(true);
	    // Vacation style cell
	    WritableFont fontDepartment = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD);
	    WritableCellFormat cellDepartment = new WritableCellFormat(fontDepartment);
	    cellDepartment.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    cellDepartment.setAlignment(Alignment.CENTRE);
	    cellDepartment.setVerticalAlignment(VerticalAlignment.CENTRE);
	    cellDepartment.setBackground(Colour.TAN);
	    // Title style cell
	    WritableFont fontTitle = new WritableFont (WritableFont.TIMES, 14, WritableFont.NO_BOLD);
	    WritableCellFormat titleStyle = new WritableCellFormat(fontTitle);
	    titleStyle.setAlignment(Alignment.CENTRE);
	    titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    // Avarage style cell
	    WritableCellFormat avarageCell = new WritableCellFormat(fontDepartment);
	    avarageCell.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    avarageCell.setAlignment(Alignment.CENTRE);
	    avarageCell.setVerticalAlignment(VerticalAlignment.CENTRE);
	    // Total style cell
	    WritableFont fontTotal = new WritableFont (WritableFont.TIMES, 14, WritableFont.BOLD);
	    WritableCellFormat totalStyle = new WritableCellFormat(fontTotal);
	    totalStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    totalStyle.setAlignment(Alignment.CENTRE);
	    totalStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    totalStyle.setBackground(Colour.GRAY_25);
		
	    // header report
	    WritableCell readCell = sheet.getWritableCell(1, 1);
		CellFormat readFormat = readCell.getCellFormat();
		label = new Label(1, 1, sheet.getCell(1, 1).getContents()+Util.getOrgInfo().getDescription(), readFormat);
		sheet.addCell(label);
	    
		// set title
	    if(HR_Department_ID != 0){
	    	label = new Label(1, 2, department.getName(), titleStyle);
	    	sheet.addCell(label);
	    }
	    else if(HR_Block_ID != 0){
	    	label = new Label(1, 2, "Блока "+block.getName(), titleStyle);
	    	sheet.addCell(label);
	    }
	       
	    AgePeriod[] lines =  getValues();
	    int col = cellStart.getC(); // offset by the number of first columns
	    int row = cellStart.getR();
	    int rowC = cellStart.getR();
	    int n = 0;
	    int num = 1;
	    int dId = 0; // Departmnet ID
	    int dep = 0;
	    for(int j = row; j < (row+lines.length); j++){
	    	 if(dId != lines[n].getDepartment_id()){
		    	   dId = lines[n].getDepartment_id();
		    	   sheet.insertRow(j+dep);
		    	   sheet.mergeCells(1, j+dep, 8, j+dep);
		    	   sheet.setRowView(j+dep, 600);
		    	   label = new Label(1, j+dep, lines[n].getDepartment(), cellDepartment); 
			       sheet.addCell(label);
			       number = new Number(9, j+dep, lines[n].getDepAvg(), cellDepartment); 
			       sheet.addCell(number);
		    	   dep++;
	    	   }   
	    	 
	    	 sheet.setRowView(j+dep, 550);
	    	 // #
	    	 number = new Number(cellStart.getC(), j+dep, num, borderStyle); 
	    	 sheet.addCell(number);
	    	 //	job name
	    	 label = new Label(col+1, j+dep, lines[n].getJob(),jobStyle);
	    	 sheet.addCell(label);
	    	 // Count < 20
	    	 number = new Number(col+2, j+dep, lines[n].getCnt20(), borderStyle); 
	    	 sheet.addCell(number);
	    	 // Count >= 20 and <= 25
	    	 number = new Number(col+3, j+dep, lines[n].getCnt20_25(), borderStyle); 
	    	 sheet.addCell(number);
	    	 // Count >= 25 and <= 30
	    	 number = new Number(col+4, j+dep, lines[n].getCnt25_30(), borderStyle); 
	    	 sheet.addCell(number);
	    	 // Count >= 30 and <= 35
	    	 number = new Number(col+5, j+dep, lines[n].getCnt30_35(), borderStyle); 
	    	 sheet.addCell(number);
	    	 // Count >= 35 and <= 45
	    	 number = new Number(col+6, j+dep, lines[n].getCnt35_45(), borderStyle); 
	    	 sheet.addCell(number);
	    	 // Count > 45
	    	 number = new Number(col+7, j+dep, lines[n].getCnt45(), borderStyle); 
	    	 sheet.addCell(number);
	    	 // Average age
	    	 if(lines[n].getJobAvg() != 0)
	    		 number = new Number(col+8, j+dep, lines[n].getJobAvg(), borderStyle);
	    	 else
	    		 number = new Number(col+8, j+dep, 0, borderStyle);
	    	 sheet.addCell(number); 		    	 
	    	 
	    	 rowC = j+dep;
	    	 n++;
	    	 num++;
	    }
	    
	    //---> Total
	    rowC++;
	    if(HR_Department_ID == 0){
	    	
	    	sheet.mergeCells(col, rowC, col+1, rowC);
	    	sheet.setRowView(rowC, 700);
	    	// Total
	    	label = new Label(col, rowC, "ИТОГО", totalStyle); 
		    sheet.addCell(label);
			//	Sum < 20
		   	number = new Number(col+2, rowC, lines[0].getSumcnt20(), totalStyle); 
		   	sheet.addCell(number);                  
		   	//	Sum >= 20 and < 25                  
		   	number = new Number(col+3, rowC, lines[0].getSumcnt20_25(), totalStyle); 
		   	sheet.addCell(number);                  
		   	//	Sum >= 25 and < 30                  
		   	number = new Number(col+4, rowC, lines[0].getSumcnt25_30(), totalStyle); 
		   	sheet.addCell(number);                  
		   	//	Sum >= 30 and < 35                  
		   	number = new Number(col+5, rowC, lines[0].getSumcnt30_35(), totalStyle); 
		   	sheet.addCell(number);                  
		   	//	Sum >= 35 and <= 45                 
		   	number = new Number(col+6, rowC, lines[0].getSumcnt35_45(), totalStyle); 
		   	sheet.addCell(number);
		   	//	Sum > 45
		   	number = new Number(col+7, rowC, lines[0].getSumcnt45(), totalStyle); 
		   	sheet.addCell(number);
		   	// Average age for Company
		   	if(lines[0].getCompanyAvg() != 0)
		   		number = new Number(col+8, rowC, lines[0].getCompanyAvg(), totalStyle);
		   	else
		   		number = new Number(col+8, rowC, 0, totalStyle);
		   	sheet.addCell(number);
		   	
		   	// Diagram
		   	formula = new Formula(col+2, 1, sheet.getName()+"!D"+(rowC+1));
			sheet1.addCell(formula);
		   	formula = new Formula(col+3, 1, sheet.getName()+"!E"+(rowC+1));
			sheet1.addCell(formula);
		   	formula = new Formula(col+4, 1, sheet.getName()+"!F"+(rowC+1));
			sheet1.addCell(formula);
		   	formula = new Formula(col+5, 1, sheet.getName()+"!G"+(rowC+1));
			sheet1.addCell(formula);
		   	formula = new Formula(col+6, 1, sheet.getName()+"!H"+(rowC+1));
			sheet1.addCell(formula);
		   	formula = new Formula(col+7, 1, sheet.getName()+"!I"+(rowC+1));
			sheet1.addCell(formula);

	    }
	   	// <---

	    
	    
	    if(HR_Block_ID == 0 && HR_Department_ID == 0)
	    	sheet.removeRow(2);
	    
	    copy.write();
	    copy.close();
	    tableBook.close();
	    temporaryFile.delete();
	       
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
				
		return Msg.translate(m_ctx, "Success");
	}
	
	private AgePeriod[] getValues(){
		
		AgePeriod values = null;
		List<AgePeriod> list = new ArrayList<AgePeriod>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
				
		try
		{
			pstmt = DB.prepareStatement(getSql(),null);
			if (HR_Department_ID != 0){
				pstmt.setInt(1, HR_Department_ID);
			}
			else if(HR_Block_ID !=0){
				pstmt.setInt(1, HR_Block_ID);
				
			}
			rs = pstmt.executeQuery();	
					
			while (rs.next ())
			{
				values = new AgePeriod();
				values.setDepartment_id(rs.getInt(1));
				values.setDepartment(rs.getString(2));
				values.setJob_id(rs.getInt(3));
				values.setJob(rs.getString(4));
				values.setCnt20(rs.getInt(5));
				values.setAvgcnt20(rs.getDouble(6));
				values.setCnt20_25(rs.getInt(7));
				values.setAvgcnt20_25(rs.getDouble(8));
				values.setCnt25_30(rs.getInt(9));
				values.setAvgcnt25_30(rs.getDouble(10));
				values.setCnt30_35(rs.getInt(11));
				values.setAvgcnt30_35(rs.getDouble(12));
				values.setCnt35_45(rs.getInt(13));
				values.setAvgcnt35_45(rs.getDouble(14));
				values.setCnt45(rs.getInt(15));
				values.setAvgcnt45(rs.getDouble(16));
				values.setDepAvg(rs.getDouble(17));
				values.setJobAvg(rs.getInt(18));
				values.setCompanyAvg(rs.getDouble(19));
				values.setSumcnt20(rs.getInt(20));
				values.setSumcnt20_25(rs.getInt(21));
				values.setSumcnt25_30(rs.getInt(22));
				values.setSumcnt30_35(rs.getInt(23));
				values.setSumcnt35_45(rs.getInt(24));
				values.setSumcnt45(rs.getInt(25));
				list.add(values);
				values=null;
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new AgePeriod[list.size()]);
	}
	
	private String getSql(){
		
		String sql_ =  "WITH T1 AS (SELECT T.HR_DEPARTMENT_ID AS DEPARTMENT_ID, \n"+ 
				"HD.NAME AS DEPARTMENT, \n"+ 
				"T.HR_JOB_ID AS JOB_ID, \n"+ 
				"T.NAME AS JOB \n"+
				"FROM HR_JOB T \n"+ 
				"JOIN HR_DEPARTMENT HD ON HD.HR_DEPARTMENT_ID=T.HR_DEPARTMENT_ID AND HD.ISACTIVE='Y' \n"+
				"LEFT JOIN HR_EMPLOYEE R ON R.HR_JOB_ID=T.HR_JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n" +
				"WHERE T.ISACTIVE='Y' \n";

		StringBuilder sql = new StringBuilder(sql_);
		StringBuilder where = new StringBuilder();
		if (HR_Department_ID != 0){
			sql.append(" WHERE T.HR_DEPARTMENT_ID=? \n");
			param.add(HR_Department_ID);
		}
		else if(HR_Block_ID !=0){
			sql.append(" WHERE T.HR_DEPARTMENT_ID IN (SELECT NODE_ID FROM AD_TREENODEDEP WHERE PARENT_ID=?) \n");
			param.add(HR_Department_ID);
			
		}
		sql.append(where.toString());
		sql.append("GROUP BY HD.NAME, T.HR_JOB_ID \n  ORDER BY T.HR_DEPARTMENT_ID, T.HR_JOB_ID \n ), \n");
		sql.append("T2 AS( \n"+
					"SELECT A.*, ( \n"+
						"SELECT COUNT(*)  FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) < 20 and \n"+
						"R.HR_JOB_ID=A.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n"+
					") AS CNT20, \n");
		sql.append("( \n "+
						"SELECT coalesce(avg(extract(year from age(r.hrm_dateOfBirth))),0) FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) < 20 AND \n "+ 
						"R.HR_JOB_ID=A.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n "+ 
						") as avgcnt20 FROM T1 A \n");
		sql.append("), \n T3 AS( \n"+
					"SELECT B.*, ( \n"+
						"SELECT COUNT(*)  FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) >= 20 and extract(year from age(r.hrm_dateOfBirth)) < 25 AND \n"+
						"R.HR_JOB_ID=B.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n"+
					") AS CNT20_25, \n");
		sql.append("( \n "+
						"SELECT coalesce(avg(extract(year from age(r.hrm_dateOfBirth))),0) FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) >= 20 and extract(year from age(r.hrm_dateOfBirth)) < 25 AND \n "+ 
						"R.HR_JOB_ID=B.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n "+ 
						") as avgcnt20_25 FROM T2 B \n");
		sql.append("), \n T4 AS( \n"+
					"SELECT C.*, ( \n"+
						"SELECT COUNT(*)  FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) >= 25 and extract(year from age(r.hrm_dateOfBirth)) < 30 AND \n"+
						"R.HR_JOB_ID=C.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n"+
					") AS CNT25_30, \n");
		sql.append("( \n "+
						"SELECT coalesce(avg(extract(year from age(r.hrm_dateOfBirth))),0) FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) >= 25 and extract(year from age(r.hrm_dateOfBirth)) < 30 AND \n "+ 
						"R.HR_JOB_ID=C.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n "+ 
						") as avgcnt25_30 FROM T3 C \n");
		sql.append("), \n T5 AS( \n"+
					"SELECT D.*, ( \n"+
						"SELECT COUNT(*)  FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) >= 30 and extract(year from age(r.hrm_dateOfBirth)) < 35 AND \n"+
						"R.HR_JOB_ID=D.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n"+
					") AS CNT30_35, \n");
		sql.append("( \n "+
						"SELECT coalesce(avg(extract(year from age(r.hrm_dateOfBirth))),0) FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) >= 30 and extract(year from age(r.hrm_dateOfBirth)) < 35 AND \n "+ 
						"R.HR_JOB_ID=D.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n "+ 
						") as avgcnt30_35 FROM T4 D \n");
		sql.append("), \n T6 AS( \n"+
					"SELECT E.*, ( \n"+
						"SELECT COUNT(*)  FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) >= 35 and extract(year from age(r.hrm_dateOfBirth)) <= 45 AND \n"+
						"R.HR_JOB_ID=E.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n"+
					") AS CNT35_45, \n");
		sql.append("( \n "+
						"SELECT coalesce(avg(extract(year from age(r.hrm_dateOfBirth))),0) FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) >= 35 and extract(year from age(r.hrm_dateOfBirth)) <= 45 AND \n "+ 
						"R.HR_JOB_ID=E.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n "+ 
						") as avgcnt35_45 FROM T5 E \n");
		sql.append("), \n T7 AS( \n"+
					"SELECT F.*, ( \n"+
						"SELECT COUNT(*)  FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) > 45 AND \n"+
						"R.HR_JOB_ID=F.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n"+
					") AS CNT45, \n");
		sql.append("( \n "+
						"SELECT coalesce(avg(extract(year from age(r.hrm_dateOfBirth))),0) FROM HR_EMPLOYEE R WHERE  extract(year from age(r.hrm_dateOfBirth)) > 45 AND \n "+ 
						"R.HR_JOB_ID=F.JOB_ID AND R.ISACTIVE='Y' AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) \n "+ 
						") as avgcnt45 FROM T6 F \n");
		sql.append(" \n ) \n");
		sql.append("SELECT RR.*, \n"+
						 "case when \n"+
							"sum(RR.CNT20 \n"+
							"   +RR.CNT20_25 \n"+
							"   +RR.CNT25_30 \n"+
							"   +RR.CNT30_35 \n"+
							"   +RR.CNT35_45 \n"+
							"   +RR.CNT45) over (partition by rr.department_id order by rr.department_id) != 0 \n"+
							"then \n"+
							"sum(RR.AVGCNT20*RR.CNT20 \n"+
							"   +RR.AVGCNT20_25*RR.CNT20_25 \n"+
							"   +RR.AVGCNT25_30*RR.CNT25_30 \n"+
							"   +RR.AVGCNT30_35*RR.CNT30_35 \n"+
							"   +RR.AVGCNT35_45*RR.CNT35_45 \n"+
							"   +RR.AVGCNT45*RR.CNT45) over (partition by rr.department_id order by rr.department_id)/ \n"+
							"sum(RR.CNT20 \n"+
							"   +RR.CNT20_25 \n"+
							"   +RR.CNT25_30 \n"+
							"   +RR.CNT30_35 \n"+
							"   +RR.CNT35_45 \n"+
							"   +RR.CNT45) over (partition by rr.department_id order by rr.department_id) \n"+
							"else 0 end::numeric(10,2) as DepAvg, \n"+
							"case when \n"+
							"   (RR.CNT20 \n"+
							"   +RR.CNT20_25 \n"+
							"   +RR.CNT25_30 \n"+
							"   +RR.CNT30_35 \n"+
							"   +RR.CNT35_45 \n"+
							"   +RR.CNT45) != 0 \n"+
							"then ( \n"+
							"   RR.AVGCNT20*RR.CNT20 \n"+
							"   +RR.AVGCNT20_25*RR.CNT20_25 \n"+
							"   +RR.AVGCNT25_30*RR.CNT25_30 \n"+
							"   +RR.AVGCNT30_35*RR.CNT30_35 \n"+
							"   +RR.AVGCNT35_45*RR.CNT35_45 \n"+
							"   +RR.AVGCNT45*RR.CNT45)/ \n"+
							"(RR.CNT20 \n"+
							"   +RR.CNT20_25 \n"+
							"   +RR.CNT25_30 \n"+
							"   +RR.CNT30_35 \n"+
							"   +RR.CNT35_45 \n"+
							"   +RR.CNT45)\n"+
							"else 0 end as JobAvg, \n" +
							"case when sum(RR.CNT20 \n"+ 
							"   +RR.CNT20_25 \n"+
							"   +RR.CNT25_30 \n"+ 
							"   +RR.CNT30_35 \n"+ 
							"   +RR.CNT35_45 \n"+ 
							"   +RR.CNT45) over () != 0 then \n"+
							"sum( RR.AVGCNT20*RR.CNT20 \n"+ 
							"   +RR.AVGCNT20_25*RR.CNT20_25 \n"+ 
							"   +RR.AVGCNT25_30*RR.CNT25_30 \n"+ 
							"   +RR.AVGCNT30_35*RR.CNT30_35 \n"+ 
							"   +RR.AVGCNT35_45*RR.CNT35_45 \n"+ 
							"   +RR.AVGCNT45*RR.CNT45) over () / \n"+ 
							"sum(RR.CNT20 \n"+ 
							"   +RR.CNT20_25 \n"+ 
							"   +RR.CNT25_30 \n"+ 
							"   +RR.CNT30_35 \n"+ 
							"   +RR.CNT35_45 \n"+ 
							"   +RR.CNT45) over() else 0 end::numeric(10,2) as companyAvg, \n"+
							"   sum(RR.CNT20) over () as sumCNT20, \n"+
							"   sum(RR.CNT20_25) over () as sumCNT20_25, \n"+
							"   sum(RR.CNT25_30) over () as sumCNT25_30, \n"+
							"   sum(RR.CNT30_35) over () as sumCNT30_35, \n"+
							"   sum(RR.CNT35_45) over () as sumCNT35_45, \n"+
							"   sum(RR.CNT45) over () as sumCNT45 \n" +
						 "FROM T7 RR ");
		
		return sql.toString();
		
	}
	
	private class AgePeriod{
		
		private int department_id;
		private String department;
		private int job_id;
		private String job;
		private int cnt20;
		private double avgcnt20;
		private int cnt20_25;
		private double avgcnt20_25;
		private int cnt25_30;
		private double avgcnt25_30;
		private int cnt30_35;
		private double avgcnt30_35;
		private int cnt35_45;
		private double avgcnt35_45;
		private int cnt45;
		private double avgcnt45;
		private double depAvg;
		private int jobAvg;
		private double companyAvg;
		private int sumcnt20;
		private int sumcnt20_25;
		private int sumcnt25_30;
		private int sumcnt30_35;
		private int sumcnt35_45;
		private int sumcnt45;
		
		public double getDepAvg() {
			return depAvg;
		}
		public void setDepAvg(double depAvg) {
			this.depAvg = depAvg;
		}
		public int getJobAvg() {
			return jobAvg;
		}
		public void setJobAvg(int jobAvg) {
			this.jobAvg = jobAvg;
		}
		public int getDepartment_id() {
			return department_id;
		}
		public void setDepartment_id(int department_id) {
			this.department_id = department_id;
		}
		public String getDepartment() {
			return department;
		}
		public void setDepartment(String department) {
			this.department = department;
		}
		public int getJob_id() {
			return job_id;
		}
		public void setJob_id(int job_id) {
			this.job_id = job_id;
		}
		public String getJob() {
			return job;
		}
		public void setJob(String job) {
			this.job = job;
		}
		public int getCnt20() {
			return cnt20;
		}
		public void setCnt20(int cnt20) {
			this.cnt20 = cnt20;
		}
		public double getAvgcnt20() {
			return avgcnt20;
		}
		public void setAvgcnt20(double avgcnt20) {
			this.avgcnt20 = avgcnt20;
		}
		public int getCnt20_25() {
			return cnt20_25;
		}
		public void setCnt20_25(int cnt20_25) {
			this.cnt20_25 = cnt20_25;
		}
		public double getAvgcnt20_25() {
			return avgcnt20_25;
		}
		public void setAvgcnt20_25(double avgcnt20_25) {
			this.avgcnt20_25 = avgcnt20_25;
		}
		public int getCnt25_30() {
			return cnt25_30;
		}
		public void setCnt25_30(int cnt25_30) {
			this.cnt25_30 = cnt25_30;
		}
		public double getAvgcnt25_30() {
			return avgcnt25_30;
		}
		public void setAvgcnt25_30(double avgcnt25_30) {
			this.avgcnt25_30 = avgcnt25_30;
		}
		public int getCnt30_35() {
			return cnt30_35;
		}
		public void setCnt30_35(int cnt30_35) {
			this.cnt30_35 = cnt30_35;
		}
		public double getAvgcnt30_35() {
			return avgcnt30_35;
		}
		public void setAvgcnt30_35(double avgcnt30_35) {
			this.avgcnt30_35 = avgcnt30_35;
		}
		public int getCnt35_45() {
			return cnt35_45;
		}
		public void setCnt35_45(int cnt35_45) {
			this.cnt35_45 = cnt35_45;
		}
		public double getAvgcnt35_45() {
			return avgcnt35_45;
		}
		public void setAvgcnt35_45(double avgcnt35_45) {
			this.avgcnt35_45 = avgcnt35_45;
		}
		public int getCnt45() {
			return cnt45;
		}
		public void setCnt45(int cnt45) {
			this.cnt45 = cnt45;
		}
		public double getAvgcnt45() {
			return avgcnt45;
		}
		public void setAvgcnt45(double avgcnt45) {
			this.avgcnt45 = avgcnt45;
		}
		public double getCompanyAvg() {
			return companyAvg;
		}
		public void setCompanyAvg(double companyAvg) {
			this.companyAvg = companyAvg;
		}
		public int getSumcnt20() {
			return sumcnt20;
		}
		public void setSumcnt20(int sumcnt20) {
			this.sumcnt20 = sumcnt20;
		}
		public int getSumcnt20_25() {
			return sumcnt20_25;
		}
		public void setSumcnt20_25(int sumcnt20_25) {
			this.sumcnt20_25 = sumcnt20_25;
		}
		public int getSumcnt25_30() {
			return sumcnt25_30;
		}
		public void setSumcnt25_30(int sumcnt25_30) {
			this.sumcnt25_30 = sumcnt25_30;
		}
		public int getSumcnt30_35() {
			return sumcnt30_35;
		}
		public void setSumcnt30_35(int sumcnt30_35) {
			this.sumcnt30_35 = sumcnt30_35;
		}
		public int getSumcnt35_45() {
			return sumcnt35_45;
		}
		public void setSumcnt35_45(int sumcnt35_45) {
			this.sumcnt35_45 = sumcnt35_45;
		}
		public int getSumcnt45() {
			return sumcnt45;
		}
		public void setSumcnt45(int sumcnt45) {
			this.sumcnt45 = sumcnt45;
		}
		
	}

}
